<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-${liquibase.xsd-version}.xsd">

    <!-- Copyright 2018-2025 Steinar Bang                                                -->
    <!--                                                                                 -->
    <!-- Licensed under the Apache License, Version 2.0 (the "License");                 -->
    <!-- you may not use this file except in compliance with the License.                -->
    <!-- You may obtain a copy of the License at                                         -->
    <!--   http://www.apache.org/licenses/LICENSE-2.0                                    -->
    <!-- Unless required by applicable law or agreed to in writing,                      -->
    <!-- software distributed under the License is distributed on an "AS IS" BASIS,      -->
    <!-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.        -->
    <!-- See the License for the specific language governing permissions and limitations -->
    <!-- under the License.                                                              -->

    <changeSet author="sb" id="handlereg-1.0.0-accounts">
        <preConditions onFail="CONTINUE" >
            <not>
                <tableExists tableName="accounts" />
            </not>
        </preConditions>

        <createTable tableName="accounts">
            <column autoIncrement="true" name="account_id" type="INTEGER">
                <constraints primaryKey="true" primaryKeyName="account_primary_key"/>
            </column>
            <column name="username" type="VARCHAR(64)">
                <constraints nullable="false" unique="true"/>
            </column>
        </createTable>

        <createIndex indexName="accounts_id_index" tableName="accounts">
            <column name="account_id"/>
        </createIndex>

        <createIndex indexName="ix_accounts_username" tableName="accounts">
            <column name="username" type="VARCHAR(64)" />
        </createIndex>

    </changeSet>


    <changeSet author="sb" id="handlereg-1.0.0-stores">
        <preConditions onFail="CONTINUE" >
            <not>
                <tableExists tableName="stores" />
            </not>
        </preConditions>

        <createTable tableName="stores">
            <column autoIncrement="true" name="store_id" type="INTEGER">
                <constraints primaryKey="true" primaryKeyName="stores_primary_key"/>
            </column>
            <column name="store_name" type="VARCHAR(256)">
                <constraints nullable="false"/>
            </column>
            <column name="gruppe" type="INTEGER">
            </column>
            <column name="rekkefolge" type="INTEGER">
            </column>
            <column defaultValueBoolean="false" name="deaktivert" type="BOOLEAN">
                <constraints nullable="false"/>
            </column>
        </createTable>

    </changeSet>

    <changeSet author="sb" id="handlereg-1.0.0-transactions">
        <preConditions onFail="CONTINUE" >
            <not>
                <tableExists tableName="transactions" />
            </not>
        </preConditions>

        <createTable tableName="transactions">
            <column autoIncrement="true" name="transaction_id" type="INTEGER">
                <constraints primaryKey="true" primaryKeyName="transactions_primary_key"/>
            </column>
            <column name="account_id" type="INTEGER">
                <constraints nullable="false"/>
            </column>
            <column name="store_id" type="INTEGER">
                <constraints nullable="false"/>
            </column>
            <column name="transaction_time" type="TIMESTAMP" defaultValueComputed="CURRENT_TIMESTAMP">
                <constraints nullable="false"/>
            </column>
            <column name="transaction_amount" type="DOUBLE">
                <constraints nullable="false"/>
            </column>
        </createTable>

        <createIndex indexName="transactions_account_id_index" tableName="transactions">
            <column name="account_id"/>
        </createIndex>

        <createIndex indexName="transactions_store_id_index" tableName="transactions">
            <column name="store_id"/>
        </createIndex>

        <addForeignKeyConstraint baseColumnNames="account_id" baseTableName="transactions" constraintName="transactions_accountid_constraint" deferrable="false" initiallyDeferred="false" onDelete="NO ACTION" onUpdate="NO ACTION" referencedColumnNames="account_id" referencedTableName="accounts"/>

        <addForeignKeyConstraint baseColumnNames="store_id" baseTableName="transactions" constraintName="transactions_storeid_constraint" deferrable="false" initiallyDeferred="false" onDelete="NO ACTION" onUpdate="NO ACTION" referencedColumnNames="store_id" referencedTableName="stores"/>

    </changeSet>

    <changeSet author="sb" id="sum-over-year-view-derby">
        <preConditions onFail="CONTINUE" >
            <and>
                <not>
                    <viewExists viewName="sum_over_year_view" />
                </not>
                <dbms type="derby" />
            </and>
        </preConditions>

        <createView viewName="sum_over_year_view" fullDefinition="false" >select sum(t.transaction_amount) as aggregate_amount, YEAR(t.transaction_time) as aggregate_year from transactions t group by YEAR(t.transaction_time) order by YEAR(t.transaction_time)</createView>
    </changeSet>

    <changeSet author="sb" id="sum-over-year-view-postgresql">
        <preConditions onFail="CONTINUE" >
            <and>
                <not>
                    <viewExists viewName="sum_over_year_view" />
                </not>
                <dbms type="postgresql" />
            </and>
        </preConditions>

        <createView viewName="sum_over_year_view" fullDefinition="false">select sum(t.transaction_amount) as aggregate_amount, extract(year from t.transaction_time) as aggregate_year from transactions t group by extract(year from t.transaction_time) order by extract(year from t.transaction_time)</createView>
    </changeSet>

    <changeSet author="sb" id="sum-over-month-view-derby">
        <preConditions onFail="CONTINUE" >
            <and>
                <not>
                    <viewExists viewName="sum_over_month_view" />
                </not>
                <dbms type="derby" />
            </and>
        </preConditions>

        <createView viewName="sum_over_month_view" fullDefinition="false" >select sum(t.transaction_amount) as aggregate_amount, YEAR(t.transaction_time) as aggregate_year, MONTH(t.transaction_time) as aggregate_month from transactions t group by YEAR(t.transaction_time), MONTH(t.transaction_time) order by YEAR(t.transaction_time), MONTH(t.transaction_time)</createView>
    </changeSet>

    <changeSet author="sb" id="sum-over-month-view-postgresql">
        <preConditions onFail="CONTINUE" >
            <and>
                <not>
                    <viewExists viewName="sum_over_month_view" />
                </not>
                <dbms type="postgresql" />
            </and>
        </preConditions>

        <createView viewName="sum_over_month_view" fullDefinition="false" >select sum(t.transaction_amount) as aggregate_amount, extract(year from t.transaction_time) as aggregate_year, extract(month from t.transaction_time) as aggregate_month from transactions t group by extract(year from t.transaction_time), extract(month from t.transaction_time) order by extract(year from t.transaction_time), extract(month from t.transaction_time)</createView>
    </changeSet>


    <changeSet author="sb" id="handlereg-1.0.0-favourites">
        <preConditions onFail="CONTINUE" >
            <not>
                <tableExists tableName="favourites" />
            </not>
        </preConditions>

        <createTable tableName="favourites">
            <column autoIncrement="true" name="favourite_id" type="INTEGER">
                <constraints primaryKey="true" primaryKeyName="favourites_primary_key"/>
            </column>
            <column name="account_id" type="INTEGER">
                <constraints nullable="false" foreignKeyName="fk_favourites_account" references="accounts(account_id)"/>
            </column>
            <column name="store_id" type="INTEGER">
                <constraints nullable="false" foreignKeyName="fk_favourites_store" references="stores(store_id)"/>
            </column>
            <column name="rekkefolge" type="INTEGER">
                <constraints nullable="false"/>
            </column>
        </createTable>

        <createIndex indexName="favourites_account_id_index" tableName="favourites">
            <column name="account_id"/>
        </createIndex>

        <createIndex indexName="favourites_store_id_index" tableName="favourites">
            <column name="store_id"/>
        </createIndex>

    </changeSet>


</databaseChangeLog>
